﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace dbschemaupdater
{
    class DAL : System.Data.Linq.DataContext
    {
        private static System.Data.Linq.Mapping.MappingSource mappingSource = new System.Data.Linq.Mapping.AttributeMappingSource();

        #region constructors

        public DAL(string connection)
            : base(connection, mappingSource)
        {
        }

        public DAL(System.Data.IDbConnection connection)
            : base(connection, mappingSource)
        {
        }

        public DAL(string connection, System.Data.Linq.Mapping.MappingSource mappingSource)
            : base(connection, mappingSource)
        {
        }

        public DAL(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource)
            : base(connection, mappingSource)
        {
        }

        #endregion

        [global::System.Data.Linq.Mapping.Table(Name = "INFORMATION_SCHEMA.TABLES")]
        public partial class TABLE
        {
            private string _TABLE_SCHEMA;
            private string _TABLE_NAME;

            [global::System.Data.Linq.Mapping.Column(Name = "TABLE_SCHEMA", Storage = "_TABLE_SCHEMA", DbType = "NVarChar(128) NOT NULL", CanBeNull = false)]
            public string SCHEMA
            {
                get { return _TABLE_SCHEMA; }
                set { _TABLE_SCHEMA = value; }
            }

            [global::System.Data.Linq.Mapping.Column(Name = "TABLE_NAME", Storage = "_TABLE_NAME", DbType = "NVarChar(128) NOT NULL", CanBeNull = false)]
            public string NAME
            {
                get { return _TABLE_NAME; }
                set { _TABLE_NAME = value; }
            }
        }

        [global::System.Data.Linq.Mapping.Table(Name = "INFORMATION_SCHEMA.COLUMNS")]
        public partial class COLUMN
        {
            [global::System.Data.Linq.Mapping.Column(Name = "TABLE_SCHEMA", DbType = "NVarChar(128) NOT NULL", CanBeNull = false)]
            public string TABLE_SCHEMA;
            [global::System.Data.Linq.Mapping.Column(Name = "TABLE_NAME", DbType = "NVarChar(128) NOT NULL", CanBeNull = false)]
            public string TABLE_NAME;
            [global::System.Data.Linq.Mapping.Column(Name = "COLUMN_NAME", DbType = "NVarChar(128) NOT NULL", CanBeNull = false)]
            public string COLUMN_NAME;
            [global::System.Data.Linq.Mapping.Column(Name = "DATA_TYPE", DbType = "NVarChar(128) NOT NULL", CanBeNull = false)]
            public string DATA_TYPE;

            [global::System.Data.Linq.Mapping.Column(Name = "ORDINAL_POSITION", DbType = "Int NOT NULL", CanBeNull = false)]
            public int ORDINAL_POSITION;
            [global::System.Data.Linq.Mapping.Column(Name = "IS_NULLABLE", DbType = "NVarChar(128) NOT NULL", CanBeNull = false)]
            public string IS_NULLABLE;

            [global::System.Data.Linq.Mapping.Column(Name = "CHARACTER_MAXIMUM_LENGTH", DbType = "Int")]
            public System.Nullable<int> CHARACTER_MAXIMUM_LENGTH;
            [global::System.Data.Linq.Mapping.Column(Name = "NUMERIC_PRECISION", DbType = "Tinyint")]
            public System.Nullable<byte> NUMERIC_PRECISION;
            [global::System.Data.Linq.Mapping.Column(Name = "NUMERIC_SCALE", DbType = "Int")]
            public System.Nullable<int> NUMERIC_SCALE;
            [global::System.Data.Linq.Mapping.Column(Name = "DATETIME_PRECISION", DbType = "Smallint")]
            public System.Nullable<short> DATETIME_PRECISION;

            [global::System.Data.Linq.Mapping.Column(Name = "COLLATION_NAME", DbType = "NVarChar(128)")]
            public string COLLATION_NAME;

            [global::System.Data.Linq.Mapping.Column(Name = "IsIdentity", DbType = "int NOT NULL", CanBeNull = false)]
            public int IsIdentity;

            [global::System.Data.Linq.Mapping.Column(Name = "IdentSeed", DbType = "bigint")]
            public long? IdentSeed;

            [global::System.Data.Linq.Mapping.Column(Name = "IdentIncr", DbType = "bigint")]
            public long? IdentIncr;

            [global::System.Data.Linq.Mapping.Column(Name = "PrimaryKey", DbType = "int")]
            public int? PrimaryKey;
        }

        public IEnumerable<TABLE> TABLES
        {
            get
            {
                return this.ExecuteQuery<TABLE>(@"
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
");
            }
        }

        public IEnumerable<COLUMN> COLUMNS
        {
            get
            {
                return this.ExecuteQuery<COLUMN>(@"
select 
c.TABLE_SCHEMA, 
c.TABLE_NAME, 
c.COLUMN_NAME, 
c.DATA_TYPE, 
cast(c.CHARACTER_MAXIMUM_LENGTH as int) CHARACTER_MAXIMUM_LENGTH, 
cast(c.NUMERIC_PRECISION as tinyint) NUMERIC_PRECISION, 
cast(c.NUMERIC_SCALE as int) NUMERIC_SCALE, 
cast(c.DATETIME_PRECISION as smallint) DATETIME_PRECISION, 
cast(c.ORDINAL_POSITION as int) ORDINAL_POSITION, 
c.IS_NULLABLE, 
c.COLLATION_NAME, 
cast(kcu.ORDINAL_POSITION as int) PrimaryKey,
cast(columnproperty(object_id(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') as int) IsIdentity, 
columnproperty(object_id(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') IsComputed,
cast(IDENT_INCR(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)) as bigint) as IdentInrc, 
cast(IDENT_SEED(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)) as bigint) as IdentSeed
from INFORMATION_SCHEMA.COLUMNS c 
inner join INFORMATION_SCHEMA.TABLES t on t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME and t.TABLE_TYPE = 'BASE TABLE'
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.TABLE_SCHEMA = c.TABLE_SCHEMA and tc.TABLE_NAME = c.TABLE_NAME and tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and kcu.COLUMN_NAME = c.COLUMN_NAME
");
            }
        }
    }
}
